package com.aternity.geolocations.firebird;

import au.com.bytecode.opencsv.CSVWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;

/**
 * 
 * @author Daniel Orr
 */
public class FirebirdExtractValidationData {

    Connection connection;
    JdbcTemplate jdbcTemplate;
    TableauNameCache nameCache;

    public FirebirdExtractValidationData() throws Exception {
        Class.forName("org.firebirdsql.jdbc.FBDriver");
        Properties props = new Properties();
        props.setProperty("user", "SYSDBA");
        props.setProperty("password", "masterkey");
        props.setProperty("encoding", "UNICODE_FSS");
        connection = DriverManager.getConnection("jdbc:firebirdsql:localhost/3050:C:/dev/geoloc/GEOCODING.fdb", props);
        jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(connection, true));
        nameCache = new TableauNameCache(new File("tableau.entity.names.csv"));
    }

    void extractCountryStateCity() throws Exception {
        try (CSVWriter csvWriter = new CSVWriter(new OutputStreamWriter(new FileOutputStream("country_state_city.csv"), "UTF-8"))) {
            final PreparedStatement ps = connection.prepareStatement(SELECT_COUNTRY_STATE_CITY);
            final ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                int countryId = rs.getInt(1);
                int stateId = rs.getInt(2);
                int cityId = rs.getInt(3);
                csvWriter.writeNext(new String[]{nameCache.getEntityName(countryId), nameCache.getEntityName(stateId),nameCache.getEntityName(cityId)});
            }
        }
    }

    public static void main(String[] args) throws Exception {
        FirebirdExtractValidationData extractor = new FirebirdExtractValidationData();
        extractor.extractCountryStateCity();
    }

    private static final String SELECT_COUNTRY_STATE_CITY
            = "select \n"
            + "    country.ID, state.ID, city.ID\n"
            + "from \n"
            + "    \"City\" city\n"
            + "    join \"State\" state on city.\"ParentID\"=state.ID\n"
            + "    join \"Country\" country on state.\"ParentID\"=country.ID\n"
            + "order by\n"
            + "    country.ID, state.ID, city.ID";

}
